'''
Created on 8 Mar 2010

@author: iliask
'''
from __future__ import division
import sys
import random
from collections import defaultdict
from uString import isNumberFast as isNumber
from uPrettytable import PrettyTable

class PivotErrors(Exception):pass
class MissingValue(PivotErrors):pass
class BadMetricConfiguration(PivotErrors):pass
class BadInputRow(PivotErrors):pass

__version__ = '0.0.1'
__author__ = 'Ilias Karampatsos'

class PivotCell(object):
    "Primitive cell. It stores the coordinates it belongs in the Pivot"
    def __init__(self, rkey, ckey, dindex):
        self.DEBUG = False
        self.__count = 0
        self.__countnondistinct = 0
        self.__countmissing = 0
        self.__dindex = dindex
        self.__distinct = False
        self.__rkey = rkey
        self.__ckey = ckey
        self.__distinct = {}
        self.__debugstorage = []
    def __str__(self):
        r, c = self.getCoords()
        return 'cell @ (%s,%s) datacol=%s' % (r, c, self.__dindex)
    
    def getColumnKey(self):
        return self.__ckey
    def getCoords(self):
        return (self.__rkey, self.__ckey)
    def dataIndex(self):
        return self.__dindex
    def _update(self, data=None):
        self.__count += 1
        if data and self.DEBUG:
            self.__debugstorage .append(data[self.__dindex])
    def _updateMissing(self):
        self.__countmissing += 1
    def getDataCount(self):
        return self.__count
    def getDistict(self):
        return self.__distinct.keys()
    def getDataMissing(self):
        return self.__countmissing
    def getDataIndex(self):
        return self.__dindex
    def getDataStorage(self):
        return self.__debugstorage
    def getDataNonDistinct(self):
        return self.__countnondistinct
    def isDistinctValue(self, data):
        if data[self.__dindex] in self.__distinct:
            self.__countnondistinct += 1
            return False
        else:
            self.__distinct[data[self.__dindex]] = None #Store it somewhere
            return True
class PivotCellCOUNT(PivotCell):
    "Count values"
    def __init__(self, rkey, ckey, dindex=None):
        self.__dvalue = 0
        PivotCell.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        self.__updateResult(data)
    def __updateResult(self, data):
        PivotCell._update(self, data)
    def result(self):
        return PivotCell.getDataCount(self)
    def resultForDisplay(self):
        return '{0:d}'.format(self.result())
    
class PivotCellCOUNTD(PivotCellCOUNT):   
    "Count distinct values"
    def __init__(self, rkey, ckey, dindex=None):
        PivotCellCOUNT.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        if PivotCell.isDistinctValue(self, data):
            PivotCellCOUNT.addData(self, data)
class PivotCellCONCATD(PivotCellCOUNTD):   
    "Count distinct values"
    def __init__(self, rkey, ckey, dindex=None):
        PivotCellCOUNT.__init__(self, rkey, ckey, dindex)
    def result(self):
        return PivotCell.getDistict(self)

class PivotCellMIN(PivotCell):
    "Min of values."
    def __init__(self, rkey, ckey, dindex):
        self.__result = None
        PivotCell.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        self.__updateResult(data)
    def __updateResult(self, data):
        dindex = PivotCell.dataIndex(self)
        if not self.__result:
            self.__result = data[dindex]
        else:
            self.__result = min(data[dindex], self.__result)
        PivotCell._update(self, data)
    def result(self):
        return self.__result
    def resultForDisplay(self):
        return self.result()

class PivotCellMAX(PivotCell):
    "Max of values."
    def __init__(self, rkey, ckey, dindex):
        self.__result = None
        PivotCell.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        self.__updateResult(data)
    def __updateResult(self, data):
        dindex = PivotCell.dataIndex(self)
        if not self.__result:
            self.__result = data[dindex]
        else:
            self.__result = max(data[dindex], self.__result)
        PivotCell._update(self, data)
    def result(self):
        return self.__result
    def resultForDisplay(self):
        return self.result()
    
class PivotCellSUM(PivotCell):
    "Sum values."
    def __init__(self, rkey, ckey, dindex):
        self.name = 'SUM'
        self.__result = None
        PivotCell.__init__(self, rkey, ckey, dindex)
    def __str__(self):
        r, c = PivotCell.getCoords(self)
        return '%s(%s)' % (self.name, PivotCell.getColumnKey(self))
    def addData(self, data=None):
        self.__updateResult(data)
    def __updateResult(self, data):
        dindex = PivotCell.dataIndex(self)
        if not isNumber(data[dindex]):
            PivotCell._updateMissing(self)
            return
        if not self.__result:
            self.__result = float(data[dindex])
        else:
            self.__result += float(data[dindex])
        PivotCell._update(self, data)
    def result(self):
        return self.__result
    def resultForDisplay(self):
        r = self.result()
        if r:
            if int(r) == r:
                return int(r) #'{0:.2}'.format(self.result()) #roundToSignificantFigures(self.result(), 2)
            else:
                return '{0:.2f}'.format(r) #'{0:.2}'.format(self.result()) #roundToSignificantFigures(self.result(), 2)
    
class PivotCellSUMD(PivotCellSUM):
    "Sum distinct values."
    def __init__(self, rkey, ckey, dindex):
        PivotCellSUM.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        if PivotCell.isDistinctValue(self, data):
            PivotCellSUM.addData(self, data)

class PivotCellSUMCHAR(PivotCell):
    "Concatenate values."
    def __init__(self, rkey, ckey, dindex):
        self.__result = None
        PivotCell.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        self.__updateResult(data)
    def __updateResult(self, data):
        dindex = PivotCell.dataIndex(self)        
        if not self.__result:
            self.__result = str(data[dindex])
        else:
            self.__result = '.'.join([self.__result,
                                     str(data[dindex])])
        PivotCell._update(self, data)
    def result(self):
        return self.__result
    def resultForDisplay(self):
        return self.result()
class PivotCellSUMCHARD(PivotCellSUMCHAR):
    "Concatenate distinct values."
    def __init__(self, rkey, ckey, dindex):
        PivotCellSUMCHAR.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        if PivotCell.isDistinctValue(self, data):
            PivotCellSUMCHAR.addData(self, data)

class PivotCellAVG(PivotCell):
    "Incrementally calculate the mean and throw bad values as missing."
    def __init__(self, rkey, ckey, dindex):
        self.__result = None
        PivotCell.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        self.__updateResult(data)
    def __updateResult(self, data):
        dindex = PivotCell.dataIndex(self)        
        if not isNumber(data[dindex]):
            PivotCell._updateMissing(self)
            return

        if not self.__result:
            self.__result = float(data[dindex])
        else:
            oldn = PivotCell.getDataCount(self)
            newn = oldn + 1
            oldm = self.__result
            newx = float(data[dindex])
            self.__result = oldm + (newx - oldm) / newn
        PivotCell._update(self, data)
    def result(self):
        return self.__result
    def resultForDisplay(self):
        return '{0:.2}'.format(self.result()) #roundToSignificantFigures(self.result(), 2)    
class PivotCellAVGD(PivotCellAVG):
    "Incrementally calculate the mean using distinct values only and throw bad values as missing."
    def __init__(self, rkey, ckey, dindex):
        PivotCellAVG.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        if PivotCell.isDistinctValue(self, data):
            PivotCellAVG.addData(self, data)
    
class PivotCellVAR(PivotCellAVG):
    "Incrementally calculate the variance and throw bad values as missing."
    def __init__(self, rkey, ckey, dindex):
        self.__result = None
        PivotCellAVG.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        self.__updateResult(data)
    def getAVGResult(self):
        return PivotCellAVG.result(self)
    def __updateResult(self, data):
        "Incrementally calculate the variance using Knuth's algorithm"
        dindex = PivotCell.dataIndex(self)        
        if not isNumber(data[dindex]):
            PivotCell._updateMissing(self)
            return

        if self.__result is None:
            self.__result = 0
            PivotCellAVG.addData(self, data)
        else:
            n = PivotCell.getDataCount(self)
            n = n + 1
            x = float(data[dindex])
            oldmean = PivotCellAVG.result(self)
            delta = x - oldmean
            PivotCellAVG.addData(self, data)
            newmean = oldmean + delta / n
            oldvar = self.__result
            self.__result = (oldvar + delta * (x - newmean))
    def result(self):
        if PivotCell.getDataCount(self) < 2:
            return self.__result
        else:
            return self.__result / (PivotCell.getDataCount(self) - 1)
    def resultForDisplay(self):
        return '{0:.2}'.format(self.result()) #roundToSignificantFigures(self.result(), 2)
class PivotCellVARD(PivotCellVAR):
    """Incrementally calculate the variance using distinct values only 
        and throw bad values as missing."""
    def __init__(self, rkey, ckey, dindex):
        PivotCellVAR.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        if PivotCell.isDistinctValue(self, data):
            PivotCellVAR.addData(self, data)

class PivotCellSTDEV(PivotCellVAR):
    "Report the standard deviation."
    def __init__(self, rkey, ckey, dindex):
        PivotCellVAR.__init__(self, rkey, ckey, dindex)
    def result(self):
        if PivotCell.getDataCount(self) < 2:
            return PivotCellVAR.result(self)
        else:
            return pow(PivotCellVAR.result(self), 0.5)
    def resultForDisplay(self):
        return '{0:.2}'.format(self.result()) #roundToSignificantFigures(self.result(), 2)

class PivotCellSTDEVD(PivotCellSTDEV):
    "Report the standard deviation of distinct values."
    def __init__(self, rkey, ckey, dindex):
        PivotCellSTDEV.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        if PivotCell.isDistinctValue(self, data):
            PivotCellSTDEV.addData(self, data)

class PivotCellAVGSTDEV(PivotCellVAR):
    def __init__(self, rkey, ckey, dindex):
        PivotCellVAR.__init__(self, rkey, ckey, dindex)
    def result(self):
        var = PivotCellVAR.result(self)
        n = PivotCell.getDataCount(self)
        if not var:
            return None
        else:
            return pow(var / n, 0.5)
    def resultForDisplay(self):
        return '{0:.2}'.format(self.result()) #roundToSignificantFigures(self.result(), 2)

class PivotCellAVGSTDEVD(PivotCellAVGSTDEV):
    def __init__(self, rkey, ckey, dindex):
        PivotCellAVGSTDEV.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        if PivotCell.isDistinctValue(self, data):
            PivotCellAVGSTDEV.addData(self, data)

class PivotCellAVGCI(PivotCellAVGSTDEV):
    def __init__(self, rkey, ckey, dindex):
        PivotCellAVGSTDEV.__init__(self, rkey, ckey, dindex)
    def result(self):
        z = 1.96
        mean = PivotCellAVG.result(self)
        err = PivotCellAVGSTDEV.result(self)
        n = PivotCell.getDataCount(self)
        if not err:
            return None
        else:
            return [mean - z * err, mean + z * err]
    def resultForDisplay(self):
        r = self.result()
        if r:
            return '[{0:.2},{1:.2}]'.format(r[0], r[1]) #roundToSignificantFigures(self.result(), 2)
        else:
            return None

class PivotCellAVGCID(PivotCellAVGCI):
    def __init__(self, rkey, ckey, dindex):
        PivotCellAVGCI.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        if PivotCell.isDistinctValue(self, data):
            PivotCellAVGCI.addData(self, data)

class PivotCellMVR(PivotCellVAR):
    "The ratio of mean to variance."
    def __init__(self, rkey, ckey, dindex):
        PivotCellVAR.__init__(self, rkey, ckey, dindex)
    def result(self):
        mean = PivotCellAVG.result(self)
        var = PivotCellVAR.result(self)
        if not mean or not var:
            return None
        else:
            return mean / var
    def resultForDisplay(self):
        return '{0:.2}'.format(self.result()) #roundToSignificantFigures(self.result(), 2)
        
class PivotCellMVRD(PivotCellMVR):
    def __init__(self, rkey, ckey, dindex):
        PivotCellMVR.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        if PivotCell.isDistinctValue(self, data):
            PivotCellMVR.addData(self, data)

class PivotCellRANGE(PivotCellMIN, PivotCellMAX):
    def __init__(self, rkey, ckey, dindex):
        PivotCellMIN.__init__(self, rkey, ckey, dindex)
        PivotCellMAX.__init__(self, rkey, ckey, dindex)
    def addData(self, data=None):
        PivotCellMIN.addData(self, data)
        PivotCellMAX.addData(self, data)
    def result(self):
        min = PivotCellMIN.result(self)
        max = PivotCellMAX.result(self)
        try:
            result = max - min
        except TypeError:
            return None
        else:
            return result
    def resultForDisplay(self):
        return '{0:.2}'.format(self.result()) #roundToSignificantFigures(self.result(), 2)

class Pivot():
    """A generic Pivot class, made to simulate Excel tables.
        cindexes is a list of cross tab columns, either stand alone or 
        combinations of columns in lists.
        metrics is a dictionary with names of pivot cell classes
        you can pass multiple columns {'AVG':[5,6]} or a single one {'AVG':5} 
        each will be hooked to a specific column index of the data set 
    """
    def __init__(self, rindexes, rheaders=None, cindexes=None, metrics=None):
        self.__rheaders = rheaders
        self.__rindexes = rindexes
        if not cindexes:
            self.__cindexes = ['']
        else:
            self.__cindexes = cindexes        
        if not metrics:
            self.__metrics = {'COUNT':0} #Add a standard count on index 0
        else:
            self.__metrics = metrics
        self.__ctab = defaultdict(dict)
        self.__DELIM = '.'
        self.__DELIMHEADER = ',' #To generate unique keys for the headers
        self.__cols = {}
    def addRowMany(self, rows):
        for row in rows:
            self.addRow(row)
    def addRow(self, row):
        rowkey = self.__DELIM.join(map(str, [row[r] for r in self.__rindexes]))
        #columns are either stand alone or combinations in lists
        for col in self.__cindexes:
            if isinstance(col, list):
                colkey = self.__DELIMHEADER.join(['%s=%s' % (c, row[c]) for c in col])
            else:
                colkey = '%s=%s' % (col, row[col])
            self.__initPivotCell(rowkey, colkey)
            for metric in self.__ctab[rowkey][colkey]:
                metric.addData(row)
                #print rowkey, colkey, metric, metric.result(), metric.getDataCount(), metric.getDataNonDistinct(), metric.getDataMissing()#, metric.getDataStorage()
#        print colkey,['%s=%s' % (c,row[c]) for c in col]
    def __initPivotCell(self, rowkey, colkey):
        "Initialise all the required objects"
        if colkey not in self.__ctab[rowkey]:
            self.__cols[colkey] = '' #Store it
            self.__ctab[rowkey][colkey] = []
            for m, dataIndex in self.__getColumnMetrics():
                cell = self.__getCellClass(m)(rowkey, colkey, dataIndex)
                cell.__dict__['name'] = '%s %s(%s)' % (rowkey,
                                                       cell.__class__.__name__.replace('PivotCell', ''),
                                                       self.__getHeaderName(dataIndex))
                self.__ctab[rowkey][colkey].append(cell)
    def __getColumnMetrics(self):
        "returns a list of tuples with all the metrics and their relevant columns"
        dataIndexes = []            
        for m in self.__metrics.keys():
            if isinstance(self.__metrics[m], list):
                for d in self.__metrics[m]:
                    if isinstance(d, int):
                        dataIndexes.append((m, d))
                    elif isinstance(d, dict):
                        print 'TODO.Pass these options to column cell(s) %s' % d
            elif isinstance(self.__metrics[m], int):
                dataIndexes.append((m, self.__metrics[m]))
            else:
                raise BadMetricConfiguration()
        return dataIndexes
    def __getCellClass(self, metric, module=sys.modules[PivotCell.__module__]):
        "get cell class from the field key variable"
        subclass = "PivotCell%s" % metric.upper()        
        if hasattr(module, subclass):
            return getattr(module, subclass)
        else:
            raise BadMetricConfiguration('Could not find %s handler.' % metric.upper())
    def printAsTable(self):
        t = PrettyTable(self.__getHeader())
        for row in self.__ctab.keys():
            curow = []
            for r in row.split(self.__DELIM):
                curow.append(r)
            while len(curow) < len(self.__rindexes):
                curow.append['']
            for col in self.__ctab[row].keys():
                for metric in self.__ctab[row][col]:
                    curow.append(metric.resultForDisplay())
#            print self.__getHeader()
#            print map(str,curow)
            t.add_row(curow)
        print t
    def __getHeader(self):
        cols = self.__cols.keys()
        metrics = self.__metrics.keys()
        header = [''] * len(self.__rindexes) #append with blanks for the row dimensions
        for c in cols:
            for m, dataIndex in self.__getColumnMetrics():
                header.append('%s %s(%s)' % (c, m.upper(),
                                              self.__getHeaderName(dataIndex)))
        return header

    def __getHeaderName(self, rindex):
        if self.__rheaders:
            return self.__rheaders[rindex]
        else:
            return '%s' % rindex


class PivotHeaderCell():
    def __init__(self, rcombo, cname=None):
        self.__DELIMHEADER = ','
        self.__unique = {}
        self.__rcombo = rcombo
    def addData(self, data=None):
        ckey = self.__DELIMHEADER.join([data[row] for row in self.__rcombo])
        self.__unique[ckey] = [data[row] for row in self.__rcombo]
    def result(self):
#        return self.__unique
        return sorted(self.__unique.values())
    def auto(self):
        level = 1
        while self.resultUpToLevel(level):
            for up in self.resultUpToLevel(level - 1):
                for this in self.resultUpToLevel(level):
                    print level, up, this, self.resultUpToLevel(level)
                    
            level += 1
            
    def resultUpToLevel(self, level):
        try:
            levelresult = {}
            for r in self.result():
                s = [r.split(self.__DELIMHEADER)[e] for e in xrange(level + 1)]
                t = self.__DELIMHEADER.join(s)
                levelresult[t] = None
            return levelresult.keys()
        except IndexError as i:
            return []

#class PivotHeaderCell():
#    def __init__(self, rcombo, dindex=None, cname=None):
#        if isinstance(rcombo, int):
#            self.__rcombo = []
#            self.__rcombo.append(rcombo)
#        else:
#            self.__rcombo = rcombo
#        self.__DELIMHEADER = ','
#        self.__PivotCellCONCATD = None
##        PivotCellCONCATD.__init__(self, rkey, ckey, dindex)
##        for col in self.__cindexes:
##            if isinstance(col, list):
#    def addData(self, data=None):
#        rkey = ''
#        ckey = self.__DELIMHEADER.join([data[row] for row in self.__rcombo])
#        newData=[]
#        print ckey, data
#        if not self.__PivotCellCONCATD:
#            self.__PivotCellCONCATD=PivotCellCONCATD(rkey,ckey,0)
        
#        if not self.__PivotCellCONCATD:
#            self.__PivotCellCONCATD=PivotCellCONCATD()
#            self.__updateResult(data)
#    def __updateResult(self, data):
#        dindex = PivotCell.dataIndex(self)        
#        if not self.__result:
#            self.__result = str(data[dindex])
#        else:
#            self.__result = '.'.join([self.__result,
#                                     str(data[dindex])])
#        PivotCell._update(self, data)
#    def result(self):
#        pass
#        return self.__result
#    def resultForDisplay(self):
#        return self.result()

def test():
    c1 = 10
    c2 = 10
    c3 = 60
    cur = 0
    p1 = PivotHeaderCell([0, 1, 2])
#    p2 = PivotHeaderCell('','',2)
    for i in range(70, 70 + c1):
        for j in range(70, 70 + c2):
            for k in range(c3):
                r = ['A %s' % chr(i),
                     'B %s' % chr(j),
                     'C %s' % chr(random.randint(72, 80)),
                     'D %s' % chr(random.randint(74, 77)),
                     'E %s' % chr(random.randint(77, 79)),
                     chr(random.randint(50, 100)),
                     random.randint(0, 10)]
                p1.addData(r)
#                p2.addData(r)
                cur += 1
    print p1.result()

    for r in sorted(p1.result()):
        print r
    #print p1.result()
##    print p1.resultUpToLevel(0)
##    print p1.resultUpToLevel(1)
##    print p1.resultUpToLevel(12)
#    print p1.auto()
    
#    from itertools import product
#    for i in product(p1.result(),p2.result()):
#        print i
def test2():
#    p = Pivot(rindexes=[0, 1], cindexes=[], metrics={'SUM':3, 'SUMD':4, 'COUNT':4, 'COUNTD':4})
    import utils.uProgressbar as pbar
    c1 = 5
    c2 = 1
    c3 = 300
    cur = 0
    widgets = ['Loading (%s)' % (c1 * c2 * c3), pbar.Percentage(), ' ', pbar.ETA(), ' ', pbar.Bar(marker=pbar.RotatingMarker())]
    myPbar = pbar.ProgressBar(widgets=widgets, maxval=c1 * c2 * c3).start()

    p = Pivot(rindexes=[0, 1],
              cindexes=[[2, 3]], #cindexes=[2,[2,3],[3,4]],
              metrics={'SUM':5})
    for i in range(70, 70 + c1):
        for j in range(70, 70 + c2):
            for k in range(c3):
                r = ['A %s' % chr(i),
                     'B %s' % chr(j),
                     'C %s' % chr(random.randint(72, 80)),
                     'D %s' % chr(random.randint(74, 77)),
                     'E %s' % chr(random.randint(77, 79)),
                     chr(random.randint(50, 100)),
                     random.randint(0, 10) / random.randint(1, 10)]
                p.addRow(r)
                cur += 1
                myPbar.update(cur)
    p.printAsTable()
def timings():
    import timeit
    t = timeit.Timer('test()', 'from __main__ import test')
    elapsed = (10 * t.timeit(number=10) / 10)
    print "Function test() takes %0.3f micro-seconds/pass" % elapsed  # typical result = 3.829

if __name__ == '__main__':
    test()
